package ufs.dcomp.mgemeos.dao;

import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import ufs.dcomp.mgemeos.negocio.Exposicao;
import ufs.dcomp.mgemeos.negocio.Horario;
import ufs.dcomp.mgemeos.negocio.Material;

/**
 *
 * @author Luiz Marcus
 * @modificacao 14/03/11
 */
public class ExposicaoDAO implements interface_dao.IExposicao {

    private static String inserir = "insert into exposicao (titulo, descricao, vagas,tipo_expo, idLocal, idExpositor) values (?,?,?,?,?,?)";
    private static String atualizar = "update exposicao set titulo=?, descricao=?, vagas=?, tipo_expo=?, idLocal=?,"
            + " idExpositor=? where idExposicao=?";
    private static String obter_expositor_exposicoes = "select * from exposicao where idExpositor = ? ";
    private static String obter = "select * from exposicao where idExposicao = ? ";
    private static String obter_todas = "select * from exposicao ORDER BY titulo";
    private static String delete = "delete from exposicao where idExposicao = ? ";

    @Override
    public boolean atualizarExposicao(Exposicao expo) {
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;
        try {
            pst = con.prepareStatement(atualizar);
            pst.setString(1, expo.getTitulo());
            pst.setString(2, expo.getDescricao());
            pst.setInt(3, expo.getVagas());
            pst.setString(4, expo.getTipo().toString());
            pst.setInt(5, expo.getIdLocal());
            pst.setInt(6, expo.getIdExpositor());
            pst.setInt(7, expo.getIdExposicao());

            HorarioDAO h = new HorarioDAO();
            LocalDAO l = new LocalDAO();
            for (Horario horario: expo.getHorario()) {
                //HorarioDAO.atualizarHorario(horario);
                h.atualizarHorario(horario);
            }
            //LocalDAO.atualizarLocal(expo.getLocal());
            boolean atualizarLocal = l.atualizarLocal(expo.getLocal());
            pst.executeUpdate();
            pst.close();
            con.close();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return  false;

        }
    }

    @Override
    public Exposicao obterExposicao(int id) {
        Exposicao expo = null;
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;
        try {
            pst = con.prepareStatement(obter);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                expo = new Exposicao();
                expo.setIdExposicao(rs.getInt(1));
                expo.setTitulo(rs.getString(2));
                expo.setDescricao(rs.getString(3));
                expo.setVagas(rs.getInt(4));
                expo.setMateriais(new MaterialDAO().obter_todos(rs.getInt(1)));
                expo.setTipo(rs.getString(5));
                expo.setIdLocal(rs.getInt(6));
                expo.setIdExpositor(rs.getInt(7));
                expo.setHorario(new HorarioDAO().obter_Exposicao_Horario(rs.getInt(1)));
                expo.setLocal(new LocalDAO().obterLocal(expo.getIdLocal()));
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return expo;
    }

    @Override
    public boolean inserirExposicao(Exposicao expo) {
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;


        try {
            pst = con.prepareStatement(inserir);
            pst.setString(1, expo.getTitulo());
            pst.setString(2, expo.getDescricao());
            pst.setInt(3, expo.getVagas());
            pst.setString(4, expo.getTipo());
            pst.setInt(5, expo.getIdLocal());
            pst.setInt(6, expo.getIdExpositor());

                               
            pst.execute();
            pst.close();
            con.close();
            
            
            if (expo.getMateriais() != null) {
                for (Material material : expo.getMateriais()) {
                    new MaterialDAO().inserirMaterial(material);
                }
            }    
            
            if (expo.getHorario()!=null) {
              for (Horario horario : expo.getHorario()) {
            new HorarioDAO().inserirHorario(horario);
            }  
            }
            

            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

    }

    @Override
    public boolean deletarExposicao(int id) {
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst = null;

        Exposicao org2 = obterExposicao(id);

        if (org2 != null) {
            try {
                pst = con.prepareStatement(delete);
                pst.setInt(1, id);
                pst.execute();
                pst.close();
                con.close();
                return true;
            } catch (SQLException e) {

                e.printStackTrace();

                return false;
            }
        } else {
            try {
                pst.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(ExposicaoDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            return false;
        }
    }

    @Override
    public ArrayList<Exposicao> obterExposicao_todos() {
        Exposicao expo = null;
        ArrayList<Exposicao> lista_exposicoes = new ArrayList<Exposicao>();
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;
        try {
            pst = con.prepareStatement(obter_todas);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {

                expo = new Exposicao();
                expo.setIdExposicao(rs.getInt(1));
                expo.setTitulo(rs.getString(2));
                expo.setDescricao(rs.getString(3));
                expo.setVagas(rs.getInt(4));
                expo.setMateriais(new MaterialDAO().obter_todos(rs.getInt(1)));
                expo.setTipo(rs.getString(5));
                expo.setIdLocal(rs.getInt(6));
                expo.setIdExpositor(rs.getInt(7));
                expo.setHorario(new HorarioDAO().obter_Exposicao_Horario(rs.getInt(1)));
                expo.setLocal(new LocalDAO().obterLocal(expo.getIdLocal()));
                lista_exposicoes.add(expo);
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lista_exposicoes;
    }

    @Override
    public ArrayList<Exposicao> obter_Expositor_Exposicoes(int id) {
        Exposicao expo = null;
        ArrayList<Exposicao> lista_exposicoes = new ArrayList<Exposicao>();
        Conexao conexao = new Conexao();
        Connection con = conexao.getConnection();
        PreparedStatement pst;
        try {
            pst = con.prepareStatement(obter_expositor_exposicoes);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {

                expo = new Exposicao();
                expo.setIdExposicao(rs.getInt(1));
                expo.setTitulo(rs.getString(2));
                expo.setDescricao(rs.getString(3));
                expo.setVagas(rs.getInt(4));
                expo.setMateriais(new MaterialDAO().obter_todos(rs.getInt(1)));
                expo.setTipo(rs.getString(5));
                expo.setIdLocal(rs.getInt(6));
                expo.setIdExpositor(rs.getInt(7));
                expo.setHorario(new HorarioDAO().obter_Exposicao_Horario(rs.getInt(1)));
                expo.setLocal(new LocalDAO().obterLocal(expo.getIdLocal()));
                lista_exposicoes.add(expo);
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lista_exposicoes;
    }

   
}
